package attend.dao.common;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.HashMap;
import java.util.List;

import attend.com.CodeMetrix;

import util.DBConnection;
import exception.BaseException;

/**
 * <pre>
 * 근태관리 공통 쿼리
 * </pre>
 * @author Yang ChangHwan
 * @since 2009-01-09
 */
public class CommonDao extends DBConnection {
	/**
	 * Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(CommonDao.class);
	
	private StringBuffer 						sql 		= 	new StringBuffer();
	private List<HashMap<String, String>> 		list 		= 	new ArrayList<HashMap<String,String>>();
	private String								yyyymmdd	=	null;	//기준일자
	private String								yyyy_mm_dd	=	null;	//기준일자 구분자 포
	private String                           vaca_in_out_tp =   null;   // 출퇴유형  1/19
	private Connection 							con 		= 	null;
	private PreparedStatement 					pstmt 		= 	null;
	private ResultSet 							rs 			= 	null;
	
	private	int									rowCount;
	private String 								msg;					//
	private String 								maxGroupCd;				//대표코드 MAX
	private String 								maxGroupCdPlusOne;		//대표코드 MAX + 1
	private HashMap<String, String> 			hm_high_cd;
	
	private List<HashMap<String, String>> 		alloclist 		= 	new ArrayList<HashMap<String,String>>();
	
	private String								crud_type;				//CRUD TYPE

	private String                              reqYyyymmdd;            //요청일자
	
	/**
	 * @throws BaseException
	 * @throws SQLException
	 */
	public CommonDao() throws BaseException, SQLException {	}
	
	/**
	 * <pre>
	 * 사업그룹 목록
	 * 사업자 그룹이 Y인 경우에만 부서를 추가할수 있게 함.
	 * </pre>
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retrieveGroupByUseY() throws BaseException, SQLException {

		try {
//			/list	=	new ArrayList<HashMap<String,String>>();
			
			con = getDataSource().getConnection();	//근태관리 Connection
			
			sql.setLength(0);
			
			sql.append("   SELECT                                 \r\n");
			sql.append("           UPPER_DEPT_NO   GROUP_NO       \r\n");
			sql.append("       ,   DEPT_NM         GROUP_NM       \r\n");
			sql.append("   FROM                                   \r\n");
			sql.append("           TB_DEPT                        \r\n");
			sql.append("   WHERE                                  \r\n");
			sql.append("           UPPER_DEPT_NO   =   DEPT_NO    \r\n");
			sql.append("       AND                                \r\n");
			sql.append("           USE_YN          =   'Y'        \r\n");

			System.out.println("======================================\r\n");
			System.out.println("그룹코드 쿼리실행 내용\r\n" + sql.toString());
			System.out.println("======================================\r\n");
			
			pstmt 	= con.prepareStatement(sql.toString());
			rs 		= pstmt.executeQuery();
			
			while (rs.next()) {
				//각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("upper_dept_no" 	, rs.getString("GROUP_NO") );	//그룹코드
				hash_map.put("dept_nm"			, rs.getString("GROUP_NM") );	//그룹명
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
		} catch (SQLException se){
				se.printStackTrace();
				BaseException dex = new BaseException();                
				dex.setException(se);
				dex.setAppReturnCode("10");
				throw dex;
			} catch(Exception e){
				e.printStackTrace();
				BaseException dex = new BaseException();                
				dex.setException(e);
				dex.setAppReturnCode("20");
				throw dex;
		} finally{
			this.close(con);
			this.close(pstmt);
			this.close(rs);
		}
	}	//retrieveUpperDept END.
	
	/**
	 * 년도  조회
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retrieveYearList() throws BaseException {
		
		try {

			con = getDataSource().getConnection();	//근태관리 Connection
			
			sql.setLength(0);
			
			sql.append("    SELECT  DISTINCT                \r\n");
			sql.append("            LEFT(YYYYMMDD ,4) YEAR  \r\n");
			sql.append("        ,   LEFT(YYYYMMDD ,4) YYYY  \r\n");
			sql.append("    FROM                            \r\n");
			sql.append("            TB_ATTEND               \r\n");
			sql.append("    ORDER BY 1 DESC                 \r\n");
			
			System.out.println("======================================\r\n");
			System.out.println("쿼리실행 내용\r\n" + sql.toString());
			System.out.println("======================================\r\n");

			pstmt 	= con.prepareStatement(sql.toString());
			rs 		= pstmt.executeQuery();
			
			list = new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("year" 	, rs.getString("YEAR") );	//연도 key
				hash_map.put("yyyy"		, rs.getString("YYYY") );	//연도 value
			
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
    	
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if (con != null) con.close();
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * 월  조회
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retrieveMonthList() throws BaseException , SQLException {
		
		
		con = getDataSource().getConnection();	//근태관리 Connection
		
		try {
			
			sql.setLength(0);
			
			sql.append("   SELECT DISTINCT                        \r\n");
			sql.append("   		SUBSTRING(YYYYMMDD , 5 , 2 ) MM1  \r\n");
			sql.append("   	,	SUBSTRING(YYYYMMDD , 5 , 2 ) MM2  \r\n");
			sql.append("   FROM                                   \r\n");
			sql.append("   		TB_ATTEND                         \r\n");
		sql.append("   	WHERE SUBSTRING(YYYYMMDD , 1 , 4 )    = '2010'                            \r\n");
			//sql.append("   ORDER BY 	1	                          \r\n");
			sql.append("   GROUP BY YYYYMMDD                            \r\n");



			
			System.out.println("======================================\r\n");
			System.out.println("쿼리실행 내용\r\n" + sql.toString());
			System.out.println("======================================\r\n");
			
			pstmt 	= con.prepareStatement(sql.toString());
			rs 		= pstmt.executeQuery();
			
			list = new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("month" 	, rs.getString("MM1") );	//연도 key
				hash_map.put("mm"		, rs.getString("MM2") );	//연도 value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
			
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	
	/**
	 * <pre>
	 * 사용가능한 그룹들만 조회함.
	 * retrieveGroupByUseY Method와 동일
	 * </pre>
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retrieveGroupList() throws BaseException , SQLException {
		
		con = getDataSource().getConnection();	//근태관리 Connection
		
		list = new ArrayList<HashMap<String,String>>();
		
		try {
			
			sql.setLength(0);
			
			sql.append("   SELECT                                 \r\n");
			sql.append("           UPPER_DEPT_NO   GROUP_NO       \r\n");
			sql.append("       ,   DEPT_NM         GROUP_NM       \r\n");
			sql.append("   FROM                                   \r\n");
			sql.append("           TB_DEPT                        \r\n");
			sql.append("   WHERE                                  \r\n");
			sql.append("           UPPER_DEPT_NO   =   DEPT_NO    \r\n");
			sql.append("       AND                                \r\n");
			sql.append("           USE_YN          =   'Y'        \r\n");

			System.out.println("======================================\r\n");
			System.out.println("--그룹코드 조회--\r\n" + sql.toString());
			System.out.println("======================================\r\n");
			
			pstmt 	= con.prepareStatement(sql.toString());
			rs 		= pstmt.executeQuery();
			
			while (rs.next()) {
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("group_no" 	, rs.getString("GROUP_NO") );	//그룹코드
				hash_map.put("group_nm"		, rs.getString("GROUP_NM") );	//그룹명
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
			
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * 사용가능한 그룹들만 조회함.
	 * retrieveGroupByUseY Method와 동일
	 * </pre>
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retrieveGroupList(String use_yn) throws BaseException , SQLException {
		
		con = getDataSource().getConnection();	//근태관리 Connection
		
		list = new ArrayList<HashMap<String,String>>();
		
		try {
			
			sql.setLength(0);
			
			sql.append("   SELECT                                 \r\n");
			sql.append("           UPPER_DEPT_NO   GROUP_NO       \r\n");
			sql.append("       ,   DEPT_NM         GROUP_NM       \r\n");
			sql.append("   FROM                                   \r\n");
			sql.append("           TB_DEPT                        \r\n");
			sql.append("   WHERE                                  \r\n");
			sql.append("           UPPER_DEPT_NO   =   DEPT_NO    \r\n");
			sql.append("       AND                                \r\n");
			
			if (use_yn.equals(CodeMetrix.ALL)) {
				sql.append("           USE_YN          =   '"+use_yn+"'        \r\n");
			}
			
			System.out.println("======================================\r\n");
			System.out.println("--그룹코드 조회--\r\n" + sql.toString());
			System.out.println("======================================\r\n");
			
			pstmt 	= con.prepareStatement(sql.toString());
			rs 		= pstmt.executeQuery();
			
			while (rs.next()) {
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("group_no" 	, rs.getString("GROUP_NO") );	//그룹코드
				hash_map.put("group_nm"		, rs.getString("GROUP_NM") );	//그룹명
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
			
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * 관리자가   권한이  있는부서만 조회하게 함. 
	 * 권태확인-관리자-그룹선택-
	 * </pre>
	 * @throws BaseException
	 * @throws SQLException
	 * @since	2009/02/24
	 */
	public void retrieveGroupListJoinAuthDept(int emp_no) throws BaseException {
		
		try {
			con = getDataSource().getConnection();	//근태관리 Connection
			
			list = new ArrayList<HashMap<String,String>>();
			
			sql.setLength(0);
			
			sql.append("				      SELECT 		G.UPPER_DEPT_NO 	GROUP_NO       					\n");
			sql.append("				      			, 	G.DEPT_NM			GROUP_NM       					\n");
			sql.append("				      FROM TB_DEPT G JOIN                            					\n");
			sql.append("				      (                                          						\n");
			sql.append("				      	SELECT distinct T.UPPER_DEPT_NO            						\n");
			sql.append("				      	FROM TB_DEPT T JOIN                        						\n");
			sql.append("				      	(                                      							\n");
			sql.append("				      			SELECT DEPT_NO                     						\n");
			sql.append("				      			FROM TB_AUTH_DEPT                  						\n");
			sql.append("				      			WHERE EMP_NO = ?                   						\n");
			sql.append("				      			AND APPROV_YN='Y'                  						\n");
			sql.append("				      	) A                                    							\n");
			sql.append("				      	ON T.DEPT_NO = A.DEPT_NO                   						\n");
			sql.append("				      	AND T.USE_YN = 'Y'                         						\n");
			sql.append("				      ) TT                                       						\n");
			sql.append("				      ON G.DEPT_NO = TT.UPPER_DEPT_NO                					\n");
			sql.append("				      AND G.USE_YN = 'Y'                             					\n");
			
			System.out.println("======================================\r\n");
			System.out.println("--권한이 부여된 그룹코드 조회--\r\n" + sql.toString());
			System.out.println("======================================\r\n");
			
			pstmt 	= con.prepareStatement(sql.toString());
			
			pstmt.setInt(1, emp_no);
			
			rs 		= pstmt.executeQuery();
			
			while (rs.next()) {
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("group_no" 	, rs.getString("GROUP_NO") );	//그룹코드
				hash_map.put("group_nm"		, rs.getString("GROUP_NM") );	//그룹명
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
			
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	
	/**
	 * <pre>권한을 가진 팀만 조회한다.</pre>
	 * @param emp_no
	 * @param group_no
	 * @throws BaseException
	 * @author Yang D
	 * @since	2009/03/19
	 */
	public void retrieveTeamListJoinAuthDept(int emp_no , int group_no) throws BaseException {
		
		try {
			con = getDataSource().getConnection();	//근태관리 Connection
			
			list = new ArrayList<HashMap<String,String>>();
			
			sql.setLength(0);
			
			sql.append("						SELECT                               								\n");
			sql.append("							    D.DEPT_NO 	TEAM_NO , D.DEPT_NM 	TEAM_NM       			\n");
			sql.append("						FROM                                 								\n");
			sql.append("						(                                    								\n");
			sql.append("						    SELECT	DEPT_NO                  								\n");
			sql.append("						    FROM	TB_AUTH_DEPT             								\n");
			sql.append("						    WHERE	EMP_NO		=	" + emp_no + "        					\n");
			sql.append("						    AND		APPROV_YN	=	'Y'      								\n");
			sql.append("						) A                                  								\n");
			sql.append("						JOIN                                 								\n");
			sql.append("							TB_DEPT D                        								\n");
			sql.append("						ON	A.DEPT_NO			=	D.DEPT_NO								\n");
			sql.append("						AND D.UPPER_DEPT_NO		=	" + group_no + "						\n");
			sql.append("						AND D.USE_YN = 'Y'													\n");
			
			System.out.println("======================================\r\n");
			System.out.println("--권한이 부여된 팀--\r\n" + sql.toString());
			System.out.println("======================================\r\n");
			
			pstmt 	= con.prepareStatement(sql.toString());
			
			rs 		= pstmt.executeQuery();
			
			while (rs.next()) {
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("team_cd" 		, rs.getString("TEAM_NO") );	//
				hash_map.put("team_nm"		, rs.getString("TEAM_NM") );	//
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
			
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * 가장 최근
	 * 기준일자 조회
	 * </pre>
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retrieveMaxYYYYMMDD() throws BaseException , SQLException {
		
		con = getDataSource().getConnection();	//근태관리 Connection
		
		try {
			
			sql.setLength(0);
			
			sql.append("   SELECT 		                                                             		     \r\n");
			sql.append("   		MAX(YYYYMMDD)														YYYYMMDD     \r\n");
			sql.append("   	,	MAX(CONVERT(VARCHAR(10) , CONVERT(SMALLDATETIME , YYYYMMDD),20))	YYYY_MM_DD   \r\n");
			sql.append("   FROM                                                                          		 \r\n");
			sql.append("   		TB_ATTEND                                                                		 \r\n");

			System.out.println("------------------------------------------------------\r\n");
			System.out.println("최근 기준일자 조회\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			pstmt 	= con.prepareStatement(sql.toString());
			rs 		= pstmt.executeQuery();
			
			while (rs.next()) {
				
				yyyymmdd = rs.getString("YYYYMMDD");		//기준일자 
				setYyyy_mm_dd(rs.getString("YYYY_MM_DD"));	//기준일자 구분자 포함
				
				//System.out.println("----->>>>>	" + yyyymmdd);
				//System.out.println("--->>>>>>	" + yyyy_mm_dd);
			}
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}       
		}
	}
	
public void retrieveGetDate() throws BaseException , SQLException {
		
		con = getDataSource().getConnection();	//근태관리 Connection
		
		try {
			
			sql.setLength(0);
			
			sql.append("   select CONVERT(VARCHAR(8) , GETDATE() , 112)             \r\n");
			                                                               	

			System.out.println("------------------------------------------------------\r\n");
			System.out.println("최근 기준일자 조회\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			pstmt 	= con.prepareStatement(sql.toString());
			rs 		= pstmt.executeQuery();
			
			while (rs.next()) {
				
				reqYyyymmdd = rs.getString(1);		//요청일자 
				
				
				//System.out.println("reqYyyymmdd----->>>>>	" + reqYyyymmdd);
				//System.out.println("--->>>>>>	" + yyyy_mm_dd);
			}
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}       
		}
	}
	/**
	 * <pre>
	 * 공통 테이블에서 그룹코드에 따른 공통코드 , 공통코드명을 가져옴.
	 * </pre>
	 * 
	 * @since 2009-01-20
	 * @param group_cd
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retireveCommonCode(String group_cd) throws BaseException , SQLException {

		con = super.getDataSource().getConnection();	//근태관리 Connection
		
		try {
			
			sql.setLength(0);
			
            sql.append("\r\n").append("    SELECT                          ");
            sql.append("\r\n").append("            CD , CD_NM              ");
            sql.append("\r\n").append("    FROM                            ");
            sql.append("\r\n").append("            TB_COMM_CD              ");
            sql.append("\r\n").append("    WHERE                           ");
            sql.append("\r\n").append("            GROUP_CD	=   ?       	");
            sql.append("\r\n").append("        AND                         ");
            sql.append("\r\n").append("            CD          !=  '0'     ");
            sql.append("\r\n").append("        AND                         ");
            sql.append("\r\n").append("            USE_YN      =  'Y' 	    ");
			
//            System.out.println("------------------------------------------------------\r\n");
//			System.out.println("그룹코드에 따른 공통코드  조회\r\n" + sql.toString());
//			System.out.println("------------------------------------------------------\r\n");

			logger.info("그룹코드에 따른 공통코드  조회");
			logger.info(sql);
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			pstmt.setString(1, group_cd);	//그룹코드//group_cd
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("cd" 		, rs.getString("CD") );		//코드 key
				hash_map.put("cd_nm"	, rs.getString("CD_NM") );	//코드명 value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
    	
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();

			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	public void retireveCommonCodeVaca( String vaca_in_out_tp) throws BaseException , SQLException {

		con = super.getDataSource().getConnection();	//근태관리 Connection
		System.out.println("CommonDao_vaca_in_out_tp-------"+vaca_in_out_tp);
		//String inout = vaca_in_out_tp.substring(0,1);
		
	    
		try {
		 if (vaca_in_out_tp.equals("I")){
			 System.out.println("11111111111111");
			 
			sql.setLength(0);
			
		    sql.append("\r\n").append("    SELECT                               ");           
		    sql.append("\r\n").append("                  CD , CD_NM             ");         
		    sql.append("\r\n").append("            FROM                         ");       
		    sql.append("\r\n").append("                  TB_COMM_CD             ");   
		    sql.append("\r\n").append("           WHERE                         "); 
		    sql.append("\r\n").append("                 GROUP_CD    =   '100'   ");
		    sql.append("\r\n").append("             AND                         ");
		    sql.append("\r\n").append("                CD          !=  '0'      ");   
		    sql.append("\r\n").append("             AND                         ");  
		    sql.append("\r\n").append("               USE_YN        =  'Y'       ");
		    sql.append("\r\n").append("             AND CD         !=  '0001'	");    
		    sql.append("\r\n").append("             AND CD         !=  '0002'    ");
		    sql.append("\r\n").append("             AND CD         !=  '0003'    ");
		    sql.append("\r\n").append("             AND CD         !=  '0006'    ");

            
            
//            System.out.println("------------------------------------------------------\r\n");
			
//			System.out.println("------------------------------------------------------\r\n");

			logger.info("그룹코드에 따른 공통코드  조회");
			logger.info(sql);
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			System.out.println("그룹코드에 따른 공통코드  조회1111\r\n" + sql.toString());
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("cd" 		, rs.getString("CD") );		//코드 key
				hash_map.put("cd_nm"	, rs.getString("CD_NM") );	//코드명 value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
		  }
		 if (vaca_in_out_tp.equals("O")){
			 System.out.println("222222222222");
				sql.setLength(0);
				
				sql.append("\r\n").append("   SELECT                             ");                        
	            sql.append("\r\n").append("          CD , CD_NM                  ");             
	            sql.append("\r\n").append("    FROM                              ");               
	            sql.append("\r\n").append("          TB_COMM_CD                  ");              
	            sql.append("\r\n").append("    WHERE                             ");               
	            sql.append("\r\n").append("          GROUP_CD = '101'            "); 
                sql.append("\r\n").append("      AND                             "); 
                sql.append("\r\n").append("             CD          !=  '0'      "); 
                sql.append("\r\n").append("      AND                             ");       
                sql.append("\r\n").append("           USE_YN      =  'Y'         "); 
                sql.append("\r\n").append("      AND    CD         !=  '1001'    "); 
                sql.append("\r\n").append("      AND    CD         !=  '1002'    "); 
                sql.append("\r\n").append("      AND    CD         !=  '1003'    "); 
                sql.append("\r\n").append("      AND    CD         !=  '1004'    "); 
                sql.append("\r\n").append("      AND    CD         !=  '1005'    "); 
                sql.append("\r\n").append("      AND    CD         !=  '1008'    "); 
	            
//	            System.out.println("------------------------------------------------------\r\n");
			
//				System.out.println("------------------------------------------------------\r\n");

				logger.info("그룹코드에 따른 공통코드  조회");
				logger.info(sql);
				
				pstmt 	= 	con.prepareStatement(sql.toString());
				
				
				/**************************************************************************/
				//P A R A M E T E R		V A L U E S		E   N   D
				/**************************************************************************/
				
				rs 		= 	pstmt.executeQuery();
				
				System.out.println("그룹코드에 따른 공통코드  조회222\r\n" + sql.toString());
				
				list 	= 	new ArrayList<HashMap<String,String>>();
				
				while (rs.next()) {
					
//					각 행의 각각컬럼의 데이터를 담는다.
					HashMap<String , String>  hash_map = new HashMap<String, String>();
					
					//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
					//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
					hash_map.put("cd" 		, rs.getString("CD") );		//코드 key
					hash_map.put("cd_nm"	, rs.getString("CD_NM") );	//코드명 value
					
					list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
				}
			  }
		 if (vaca_in_out_tp.equals("IO")){
			 System.out.println("222222222222");
				sql.setLength(0);
				
				sql.append("\r\n").append("   SELECT                             ");                        
	            sql.append("\r\n").append("          CD , CD_NM                  ");             
	            sql.append("\r\n").append("    FROM                              ");               
	            sql.append("\r\n").append("          TB_COMM_CD                  ");              
	            sql.append("\r\n").append("    WHERE                             ");               
	            sql.append("\r\n").append("          GROUP_CD = '102'            "); 
                sql.append("\r\n").append("      AND                             "); 
                sql.append("\r\n").append("             CD          !=  '0'      "); 
                sql.append("\r\n").append("      AND                             ");       
                sql.append("\r\n").append("           USE_YN      =  'Y'         "); 
              
//	            System.out.println("------------------------------------------------------\r\n");
			
//				System.out.println("------------------------------------------------------\r\n");

				logger.info("그룹코드에 따른 공통코드  조회");
				logger.info(sql);
				
				pstmt 	= 	con.prepareStatement(sql.toString());
				
				
				/**************************************************************************/
				//P A R A M E T E R		V A L U E S		E   N   D
				/**************************************************************************/
				
				rs 		= 	pstmt.executeQuery();
				
				System.out.println("그룹코드에 따른 공통코드  조회222\r\n" + sql.toString());
				
				list 	= 	new ArrayList<HashMap<String,String>>();
				
				while (rs.next()) {
					
//					각 행의 각각컬럼의 데이터를 담는다.
					HashMap<String , String>  hash_map = new HashMap<String, String>();
					
					//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
					//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
					hash_map.put("cd" 		, rs.getString("CD") );		//코드 key
					hash_map.put("cd_nm"	, rs.getString("CD_NM") );	//코드명 value
					
					list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
				}
			  }
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();

			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	/**
	 * 하위코드조회
	 * @param subCode
	 * @throws BaseException
	 */
	public void retrieveCommonSubCode(String subCode) throws BaseException {
		
		try {
			con = super.getDataSource().getConnection();	//근태관리 Connection
			
			sql.setLength(0);
			
			sql.append("			  SELECT                               				\r\n");
			sql.append("			  		CD , CD_NM                   				\r\n");
			sql.append("			  FROM                                 				\r\n");
			sql.append("			  		TB_COMM_CD                   				\r\n");
			sql.append("			  WHERE                                				\r\n");
			sql.append("			  		 CD		=   '"+subCode+"'              		\r\n");
			sql.append("			  	AND	USE_YN	=	'Y'              				\r\n");
			
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
//			System.out.println("------------------------------------------------------\r\n");
//			System.out.println("하위 코드  조회\r\n" + sql.toString()	);
//			System.out.println("------------------------------------------------------\r\n");
			
			logger.info("하위 코드  조회");
			logger.info(sql);
			
			pstmt 	= 	con.prepareStatement( sql.toString() );
			rs 		= 	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				hash_map.put("cd" 		, rs.getString("CD") );		//코드 key
				hash_map.put("cd_nm"	, rs.getString("CD_NM") );	//코드명 value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
    	
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();

			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * 공통코드에서 여러 그룹코드들의 cd , cd_nm을 가져온다.
	 * java 5 or high version...
	 * <pre>
	 * @param strings	-	varargs
	 * @since	2009/02/09
	 */
	public void retireveCommonCode(String... group_cds ) throws BaseException {
		
		try {
			con = super.getDataSource().getConnection();	//근태관리 Connection
			
			sql.setLength(0);
			
			sql.append("			  SELECT                               				\r\n");
			sql.append("			  		CD , CD_NM                   				\r\n");
			sql.append("			  FROM                                 				\r\n");
			sql.append("			  		TB_COMM_CD                   				\r\n");
			sql.append("			  WHERE                                				\r\n");
			sql.append("			  		GROUP_CD	IN	(	#1 )  					\r\n");
			sql.append("			  	AND CD		!=	'0'              				\r\n");
			sql.append("			  	AND	USE_YN	=	'Y'              				\r\n");
			
			

			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			String query	=	"";
			
			for (int i = 0; i < group_cds.length; i++) {
				
				if (i < group_cds.length -1) {
					query += "'" + group_cds[i] + "' , ";					
				} else {
					query += "'" + group_cds[i] + "'";
				}
				
			}
			//#1을 치환함.
			query	=	sql.toString().replace("#1", query);
			
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("그룹코드들에 따른 공통코드  조회\r\n" + query	);
			System.out.println("------------------------------------------------------\r\n");
			
			pstmt 	= 	con.prepareStatement( query );
			rs 		= 	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("cd" 		, rs.getString("CD") );		//코드 key
				hash_map.put("cd_nm"	, rs.getString("CD_NM") );	//코드명 value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
    	
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();

			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}

	
	/**
	 * 휴가목록을 조회
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retrieveVacationCode(int group_no) throws BaseException , SQLException {
		
		con = super.getDataSource().getConnection();	//근태관리 Connection
		
		try {
			
			sql.setLength(0);
			
			sql.append("    SELECT                      \r\n");
			sql.append("    		CD                  \r\n");
			sql.append("    	,	CD_NM               \r\n");
			sql.append("    FROM                        \r\n");
			sql.append("    		TB_COMM_CD          \r\n");
			sql.append("    WHERE                       \r\n");
			sql.append("    		GROUP_CD = '102'    \r\n");
			sql.append("    	AND                     \r\n");
			sql.append("    		USE_YN = 'Y'        \r\n");
			sql.append("    	AND                     \r\n");
			sql.append("    		CD	<>	'0'         \r\n");
			sql.append("    	AND  (                  \r\n");
			sql.append("    		CD	=	'2001'      \r\n");
			sql.append("    	OR                     	\r\n");
			sql.append("    		CD	=	'2002'  )   \r\n");
			
            System.out.println("------------------------------------------------------\r\n");
			System.out.println("휴가목록을 조회\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");

			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("cd" 		, rs.getString("CD") );		//코드 key
				hash_map.put("cd_nm"	, rs.getString("CD_NM") );	//코드명 value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
    	
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();

			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
		
	}
	
	/**
	 * 휴가목록을 조회
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void allocNumList() throws BaseException {
		
		
		try {
			
			alloclist 	= 	new ArrayList<HashMap<String,String>>();
			
			int i = 0;
			String tmp_num = "5.0";
			
			for(i=0; 21 > i; i++){
				
				if(!tmp_num.equals("0.0")){
					
					//각 행의 각각컬럼의 데이터를 담는다.
					HashMap<String , String>  hash_map = new HashMap<String, String>();
					
					//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
					//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
					hash_map.put("alloc_num" 	, tmp_num);		//코드 key
					hash_map.put("alloc_num_nm"	, tmp_num);	//코드명 value
					
					alloclist.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
				}	
					tmp_num = (Float.parseFloat(tmp_num) - 0.5) + "";
				
				
				//System.out.println("tmp_num : "+tmp_num);
			}
    	
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
	 }
		
	}
	
	/**
	 * 휴가목록을 조회
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retrieveVacationCode() throws BaseException , SQLException {
		
		con = super.getDataSource().getConnection();	//근태관리 Connection
		
		try {
			
			sql.setLength(0);
			
			sql.append("    SELECT                      \r\n");
			sql.append("    		CD                  \r\n");
			sql.append("    	,	CD_NM               \r\n");
			sql.append("    FROM                        \r\n");
			sql.append("    		TB_COMM_CD          \r\n");
			sql.append("    WHERE                       \r\n");
			sql.append("    		GROUP_CD = '102'    \r\n");
			sql.append("    	AND                     \r\n");
			sql.append("    		USE_YN = 'Y'        \r\n");
			sql.append("    	AND                     \r\n");
			sql.append("    		CD	!=	'0'         \r\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("휴가목록을 조회\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("cd" 		, rs.getString("CD") );		//코드 key
				hash_map.put("cd_nm"	, rs.getString("CD_NM") );	//코드명 value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
		
	}
	
	/**
	 * <pre>
	 * 사원list조회
	 * </pre>
	 * @since 2009/0121
	 * @author Yang D
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retrieveEmpNoByGroupCdAndTeamCd(String sgroup , String steam) throws BaseException , SQLException {
		
		con = super.getDataSource().getConnection();	//근태관리 Connection
		
		try {
			
			sql.setLength(0);
			
			
			sql.append("		SELECT																					\n");
			sql.append("				E.EMP_NO																		\r\n");
			sql.append("			,	E.EMP_NM																		\r\n");
			sql.append("		FROM TB_EMP E																			\r\n");
			sql.append("			JOIN TB_DEPT T																		\r\n");
			sql.append("				ON	E.FK_DEPT_NO = T.DEPT_NO													\r\n");
			sql.append("					AND E.WORK_YN			= 'Y'						--사원유무  			\r\n");
			sql.append("					AND T.USE_YN			= 'Y'						--사용유무  			\r\n");
			sql.append("					AND E.EMP_NO 			<>		1					--관리자 사번 제외		\r\n");
			sql.append("					AND		T.DEPT_NO	LIKE	REPLACE(? , 'ALL' , '')		--팀				\r\n");
			sql.append("			JOIN	TB_DEPT G																	\r\n");
			sql.append("				ON T.UPPER_DEPT_NO = G.DEPT_NO													\r\n");
			sql.append("					AND G.UPPER_DEPT_NO LIKE	REPLACE(? , 'ALL' , '')		--그룹				\r\n");
			sql.append("					AND G.USE_YN			= 'Y'	--사원유무  								\r\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("사원 목록 조회\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			System.err.println(sgroup + "			<<<<<<<<<<<---sgroupsgroupsgroupsgroup");
			System.err.println(steam + "			<<<<<<<<<<<---steamsteamsteamsteamsteam");
	
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			pstmt.setString(1, steam);
			pstmt.setString(2, sgroup);
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				
	//			각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("emp_no" 		, rs.getString("EMP_NO") );		// key
				hash_map.put("emp_nm"		, rs.getString("EMP_NM") );		// value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
		
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
	
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * 사원list조회
	 * </pre>
	 * @since 2009/0121
	 * @author Yang D
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retrieveEmpNo() throws BaseException , SQLException {
		
		con = super.getDataSource().getConnection();	//근태관리 Connection
		
		try {
			
			sql.setLength(0);
			
			sql.append("    SELECT                       \r\n");
			sql.append("    		EMP_NO               \r\n");
			sql.append("    	,	EMP_NM               \r\n");
			sql.append("    FROM                         \r\n");
			sql.append("    		TB_EMP               \r\n");
			sql.append("    WHERE                        \r\n");
			sql.append("    		WORK_YN = 'Y'        \r\n");
			sql.append("    	AND                      \r\n");
			sql.append("    		RETIRE_DT IS NULL    \r\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("사원list조회\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				
				//			각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("emp_no" 		, rs.getString("EMP_NO") );		// key
				hash_map.put("emp_nm"		, rs.getString("EMP_NM") );		// value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * 팀을 조회한다.
	 * </pre>
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retrieveTeam() throws BaseException , SQLException {
		
		con	=	super.getDataSource().getConnection();
		
		try {
			
			sql.setLength(0);
			
			sql.append("   SELECT                                           \r\n");
			sql.append("        DEPT_NO TEAM_CD                             \r\n");
			sql.append("    ,   DEPT_NM TEAM_NM                             \r\n");
			sql.append("   FROM                                             \r\n");
			sql.append("        TB_DEPT                                     \r\n");
			sql.append("   WHERE                                            \r\n");
			sql.append("        UPPER_DEPT_NO != DEPT_NO                    \r\n");
			sql.append("    AND                                             \r\n");
			sql.append("        USE_YN = 'Y'                                \r\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("--TEAM	list조회--\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
	
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				
	//			각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("team_cd" 		, rs.getString("TEAM_CD") );		// key
				hash_map.put("team_nm"		, rs.getString("TEAM_NM") );		// value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
		
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
	
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * 그룹코드에 따른 팀을 조회한다.
	 * </pre>
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retrieveTeamByGroupCd(String sgroup) throws BaseException , SQLException {
		
		con	=	super.getDataSource().getConnection();
		
		try {
			
			sql.setLength(0);
			
			sql.append("   SELECT                                           		\r\n");
			sql.append("        DEPT_NO TEAM_CD                             		\r\n");
			sql.append("    ,   DEPT_NM TEAM_NM                             		\r\n");
			sql.append("   FROM                                             		\r\n");
			sql.append("        TB_DEPT                                     		\r\n");
			sql.append("   WHERE                                            		\r\n");
			sql.append("        UPPER_DEPT_NO != DEPT_NO                    		\r\n");
			sql.append("    AND                                             		\r\n");
			sql.append("        USE_YN = 'Y'                                		\r\n");
			sql.append("    AND UPPER_DEPT_NO   LIKE	REPLACE(? , 'ALL' , '%')   \r\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("--TEAM	list조회--\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			System.err.println(sgroup + "			<<<<<<<<<<<---sgroupsgroupsgroupsgroup");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			pstmt.setString(1, sgroup);
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				
				//			각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("team_cd" 		, rs.getString("TEAM_CD") );		// key
				hash_map.put("team_nm"		, rs.getString("TEAM_NM") );		// value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * 그룹코드에 따른   권한 팀을 조회한다.
	 * </pre>
	 * @throws BaseException
	 * @throws SQLException
	 */
	public void retrieveTeamByGroupCdAuth(String sgroup , int emp_no) throws BaseException , SQLException {
		
		con	=	super.getDataSource().getConnection();
		
		try {
			
			sql.setLength(0);
			
			sql.append("				SELECT 		T.DEPT_NO 	TEAM_CD     								\r\n");
			sql.append("					   , 	T.DEPT_NM 	TEAM_NM     								\r\n");
			sql.append("				FROM                                								\r\n");
			sql.append("				(                                   								\r\n");
			sql.append("					SELECT DEPT_NO                  								\r\n");
			sql.append("					FROM TB_AUTH_DEPT               								\r\n");
			sql.append("					WHERE EMP_NO =?		               								\r\n");
			sql.append("					AND APPROV_YN = 'Y'             								\r\n");
			sql.append("				) A                                 								\r\n");
			sql.append("				JOIN TB_DEPT T                      								\r\n");
			sql.append("				ON A.DEPT_NO = T.DEPT_NO            								\r\n");
			sql.append("				 AND UPPER_DEPT_NO   LIKE	REPLACE(? , 'ALL' , '%')					\r\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("--TEAM	list조회--\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			System.err.println(sgroup + "			<<<<<<<<<<<---sgroupsgroupsgroupsgroup");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			pstmt.setInt(1, emp_no);
			pstmt.setString(2, sgroup);
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				
				//			각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("team_cd" 		, rs.getString("TEAM_CD") );		// key
				hash_map.put("team_nm"		, rs.getString("TEAM_NM") );		// value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * 부서조회
	 * 그룹
	 * 그룹/팀
	 * 한꺼번에 조회됨.
	 * </pre>
	 * @param group_cd
	 * @throws BaseException
	 * @throws SQLException
	 * @since	2009/02/02
	 * @author Yang D
	 */
	public void retireveDept() throws BaseException , SQLException {

		con = super.getDataSource().getConnection();	//근태관리 Connection
		
		try {
			
			sql.setLength(0);
			
			sql.append("    SELECT                                                              \r\n");
            sql.append("    		D1.DEPT_NO                                                  \r\n");
            sql.append("    	,	CASE                                                        \r\n");
            sql.append("    		WHEN D2.DEPT_NM = D1.DEPT_NM		THEN D2.DEPT_NM         \r\n");
            sql.append("    		ELSE D2.DEPT_NM + '/' + D1.DEPT_NM	END		DEPT_NM         \r\n");
            sql.append("    FROM                                                                \r\n");
            sql.append("    	TB_DEPT D1 JOIN TB_DEPT D2 ON D1.UPPER_DEPT_NO = D2.DEPT_NO     \r\n");
            sql.append("        AND D1.USE_YN = 'Y'												\r\n");
            sql.append("    	AND D2.USE_YN = 'Y'												\r\n");
            sql.append("    	AND D1.DEPT_NO != D1.UPPER_DEPT_NO								\r\n");
            
            System.out.println("------------------------------------------------------\r\n");
			System.out.println("그룹/팀  조회							\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");

			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String,String>>();
			
			while (rs.next()) {
				
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("dept_no" 		, rs.getString("DEPT_NO") );		//코드 key
				hash_map.put("dept_nm"		, rs.getString("DEPT_NM") );		//코드명 value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
    	
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();

			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * 공통코드 테이블에서
	 * 대표코드만 조회함.
	 * </pre>
	 * @throws BaseException
	 * @author Yang D
	 * @since	2009/02/10
	 */
	public void retrieveCommonGroupCd() throws BaseException {
		try {
			
			con	=	super.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("	SELECT                     	\r\n");
			sql.append("		GROUP_CD CD , CD_NM		\r\n");
			sql.append("	FROM                       	\r\n");
			sql.append("		TB_COMM_CD         		\r\n");
			sql.append("	WHERE CD = '0'             	\r\n");
            
            System.out.println("------------------------------------------------------\r\n");
			System.out.println("공통코드 조회\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");

			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String , String>>();
			
			while (rs.next()) {
				
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("cd" 		, rs.getString("CD") );		//코드 key
				hash_map.put("cd_nm"	, rs.getString("CD_NM") );	//코드명 value
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
    	
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();

			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * 공통코드 테이블 결과조회
	 * </pre>
	 * @param high_cd	-	대표코드
	 * @param cd_nm		-	하위코드명
	 * @throws BaseException
	 * @since	2009/02/10
	 */
	public void retrieveCommonTable(String high_cd , String cd_nm , int s_row , int e_row) throws BaseException {
		
		try {
			
			con	=	super.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("	SELECT	Z.*																		\r\n");
			sql.append("	FROM	(																		\r\n");
			sql.append("	SELECT	ROW_NUMBER() OVER(ORDER BY L.GROUP_CD)         ROWNUM					\r\n");
			sql.append("		,	L.GROUP_CD		HIGH_CD                                     			\r\n");
			sql.append("		,	F.HIGH_NM                                                   			\r\n");
			sql.append("		,	L.CD                                                        			\r\n");
			sql.append("		,	L.CD_NM                                                     			\r\n");
			sql.append("		,	L.USE_YN                                                    			\r\n");
			sql.append("		,	ETC                                                         			\r\n");
			sql.append("	FROM                                                                    		\r\n");
			sql.append("		TB_COMM_CD L 		JOIN	                                                \r\n");
			sql.append("		(                                                                   		\r\n");
			sql.append("			SELECT GROUP_CD HIGH_CD                                     			\r\n");
			sql.append("				,	CD_NM	HIGH_NM                             					\r\n");
			sql.append("			FROM TB_COMM_CD                                             			\r\n");
			sql.append("			WHERE CD = '0'                                              			\r\n");
			sql.append("		) F                                                                 		\r\n");
			sql.append("	ON	L.GROUP_CD 		= 		F.HIGH_CD                                           \r\n");
			sql.append("		AND                                                                 		\r\n");
			sql.append("			HIGH_CD		LIKE	REPLACE(	? , 'ALL' , '%'	)        				\r\n");
			sql.append("		AND                                                                 		\r\n");
			sql.append("			L.CD_NM		LIKE (CASE ? WHEN ''  THEN '%' ELSE ? + '%' END)			\r\n");
			sql.append("	)	Z																			\r\n");
			sql.append("	WHERE																			\r\n");
			sql.append("			Z.ROWNUM BETWEEN 	?	AND 	?										\r\n");
			
            
            System.out.println("------------------------------------------------------\r\n");
			System.out.println("공통코드 테이블 결과  조회\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");

			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			int idx	=	1;
			
			pstmt.setString(idx++, high_cd);	//대표코드
			pstmt.setString(idx++, cd_nm);		//하위코드
			pstmt.setString(idx++, cd_nm);		//하위코드
			pstmt.setInt(	idx++, s_row);
			pstmt.setInt(	idx++, e_row);
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			list 	= 	new ArrayList<HashMap<String , String>>();
			
			while (rs.next()) {
				
//				각 행의 각각컬럼의 데이터를 담는다.
				HashMap<String , String>  hash_map = new HashMap<String, String>();
				
				//hash_map.put("대소문자로 구분하여 사용할 것." , "디비에서는 상관없음.");
				//jsp에서 <s:property value='upper_dept_no'/> 와 같아야 함.
				hash_map.put("high_cd" 		, rs.getString("HIGH_CD") );		//
				hash_map.put("high_nm" 		, rs.getString("HIGH_NM") );		//
				hash_map.put("cd" 			, rs.getString("CD") );		//코드 key
				hash_map.put("cd_nm"		, rs.getString("CD_NM") );	//코드명 value
				hash_map.put("use_yn"		, rs.getString("USE_YN") );	//
				hash_map.put("etc"			, rs.getString("ETC") );	//
				
				list.add(hash_map);	// 한행의 데이터인 맵객체를 담는다.
			}
    	
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();

			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * 공통코드 조회 결과 건수
	 * </pre>
	 * @param high_cd	-	대표코드
	 * @param cd_nm		-	하위코드
	 * @throws BaseException
	 */
	public void retrieveCommonTableRowCount(String high_cd , String cd_nm) throws BaseException {
		
		try {
			
			con	=	super.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("	SELECT																			\r\n");
			sql.append("		COUNT(*)			CNT                                                     \r\n");
			sql.append("	FROM                                                                    		\r\n");
			sql.append("		TB_COMM_CD L 		 JOIN                                               	\r\n");
			sql.append("		(                                                                   		\r\n");
			sql.append("			SELECT GROUP_CD HIGH_CD                                     			\r\n");
			sql.append("				,	CD_NM	HIGH_NM                             					\r\n");
			sql.append("			FROM TB_COMM_CD                                             			\r\n");
			sql.append("			WHERE CD = '0'                                              			\r\n");
			sql.append("		) F                                                                 		\r\n");
			sql.append("	ON	L.GROUP_CD 		= 		F.HIGH_CD                                           \r\n");
			sql.append("		AND                                                                 		\r\n");
			sql.append("			HIGH_CD		LIKE	REPLACE(	? , 'ALL' , '%'	)        				\r\n");
			sql.append("		AND                                                                 		\r\n");
			sql.append("			L.CD_NM		LIKE (CASE ? WHEN ''  THEN '%' ELSE ? END)					\r\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("공통코드 테이블 결과 갯수  조회\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			pstmt.setString(1, high_cd);	//대표코드
			pstmt.setString(2, cd_nm);		//하위코드
			pstmt.setString(3, cd_nm);		//하위코드
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			if (rs.next()) {
				
				rowCount	= rs.getInt("CNT");		//total row count
				
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	
	/**
	 * <pre>
	 * 
	 * </pre>
	 * @throws BaseException
	 */
	public void maxCommTableGroupCd() throws BaseException {
		
		try {
			
			con	=	super.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("	SELECT  MAX(GROUP_CD) + 1 		AS MAX_GROUP_PLUS_ONE		\r\n");
			sql.append("		,	MAX(GROUP_CD) 			AS MAX_GROUP_CD				\r\n");
			sql.append("	FROM TB_COMM_CD												\r\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("대표코드 MAX + 1  조회\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs 		= 	pstmt.executeQuery();
			
			if (rs.next()) {
				
				maxGroupCdPlusOne	= rs.getString("MAX_GROUP_PLUS_ONE");		//total row count
				maxGroupCd			= rs.getString("MAX_GROUP_CD");		//total row count
				
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * 공통코드 조회 결과 건수
	 * </pre>
	 * @param high_cd	-	대표코드명
	 * @param cd_nm		-	하위코드명
	 * @param	etc		-	비고
	 * @param	use_yn	-	사용여부
	 * @throws BaseException
	 */
	public void insertCommonTableGroupCd(String high_nm , String etc , String use_yn) throws BaseException {
		
		try {
			
			con	=	super.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("	INSERT INTO TB_COMM_CD	(GROUP_CD , CD , CD_NM , ETC , USE_YN) 		\r\n");
			sql.append("	SELECT MAX(GROUP_CD) + 1  , '0'  , ? , ? , ? 	FROM TB_COMM_CD		\r\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("공통코드 테이블 결과  조회\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			pstmt.setString(1, high_nm);		//하위코드
			pstmt.setString(2, etc);		//비고
			pstmt.setString(3, use_yn);		//사용여부
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			int i 		= 	pstmt.executeUpdate();
			
			if (i > 0) {
				msg	=	CodeMetrix.OK;
			}
			
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	

	/**
	 * <pre>
	 * 대표 그룹코드를 조회한다.
	 * </pre>
	 * @param group_cd	-	그룹코드
	 * @throws BaseException
	 */
	public void retireveCommonGroupCodeByOne(String group_cd) throws BaseException {
		
		try {
			con	=	super.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("	SELECT                                  			\r\n");
			sql.append("			GROUP_CD , CD , CD_NM , ETC , USE_YN		\r\n");
			sql.append("	FROM                                    			\r\n");
			sql.append("			TB_COMM_CD                      			\r\n");
			sql.append("	WHERE	                                			\r\n");
			sql.append("			GROUP_CD	= ?                 			\r\n");
			sql.append("		AND	                                			\r\n");
			sql.append("			CD			= '0'               			\r\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("그룹코드('" + group_cd + "')를 조회한다.\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			pstmt.setString(1, group_cd);		//그룹코드
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs	= 	pstmt.executeQuery();
			
			hm_high_cd = new HashMap<String, String>();
			
			if (rs.next()) {
				
				hm_high_cd.put("group_cd"	, rs.getString("GROUP_CD"));
				hm_high_cd.put("cd"			, rs.getString("CD"));
				hm_high_cd.put("cd_nm"		, rs.getString("CD_NM"));
				hm_high_cd.put("etc"		, rs.getString("ETC"));
				hm_high_cd.put("use_yn"		, rs.getString("USE_YN"));
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
		
	}
	
	/**
	 * <pre>
	 * 하위 그룹코드를 조회한다.
	 * </pre>
	 * @param group_cd	-	그룹코드
	 * @throws BaseException
	 */
	public void retireveCommonSubGroupCode(String group_cd , String sub_code) throws BaseException {
		
		try {
			con	=	super.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("				SELECT                                  							\n");
			sql.append("						C1.GROUP_CD                     							\n");
			sql.append("					, 	C1.CD_NM          GROUP_NM              					\n");
			sql.append("					, 	C2.CD                           							\n");
			sql.append("					, 	C2.CD_NM                        							\n");
			sql.append("					, 	C2.ETC                          							\n");
			sql.append("					, 	C2.USE_YN                       					      	\n");
			sql.append("				FROM                                    					      	\n");
			sql.append("						TB_COMM_CD C1 JOIN TB_COMM_CD C2					      	\n");
			sql.append("						ON C1.GROUP_CD	=	C2.GROUP_CD 					      	\n");
			sql.append("						AND C1.CD  		= '0'           					      	\n");
			sql.append("						AND C1.USE_YN 	= C2.USE_YN     					      	\n");
			sql.append("						AND C2.CD 		= '" + sub_code + "'        				\n");
			sql.append("						AND C1.GROUP_CD = ?		        					      	\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("대표 그룹코드('" + group_cd + "')와 '" + sub_code + "'를 조회한다.\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			pstmt.setString(1, group_cd);		//그룹코드
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs	= 	pstmt.executeQuery();
			
			hm_high_cd = new HashMap<String, String>();
			
			if (rs.next()) {
				
				hm_high_cd.put("group_cd"	, rs.getString("GROUP_CD"));
				hm_high_cd.put("group_nm"	, rs.getString("GROUP_NM"));
				hm_high_cd.put("sub_cd"		, rs.getString("CD"));
				hm_high_cd.put("sub_nm"		, rs.getString("CD_NM"));
				hm_high_cd.put("etc"		, rs.getString("ETC"));
				hm_high_cd.put("use_yn"		, rs.getString("USE_YN"));
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
		
	}
	
	

	/**
	 * <pre>
	 * 대표코드 삽입 및 수정시 
	 * 무조건 실행되게 되어있음.
	 * </pre>
	 * @param group_cd		-	그룹코드
	 * @throws BaseException
	 */
	public void deleteHighCodeByOne(String group_cd) throws BaseException {
		try {
			con	=	super.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("	DELETE FROM                  	\r\n");
			sql.append("				TB_COMM_CD       	\r\n");
			sql.append("	WHERE                        	\r\n");
			sql.append("				GROUP_CD	= ?  	\r\n");
			sql.append("		AND		CD			= '0'	\r\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("그룹코드('" + group_cd + "')를 삭제하는 쿼리\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			pstmt.setString(1, group_cd);		//그룹코드
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			int i	= 	pstmt.executeUpdate();
			
			if (i > 0) {
				setMsg(CodeMetrix.OK);
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}
	
	/**
	 * <pre>
	 * yyyy-MM-dd 구하기
	 * </pre>
	 * @since	2009/02/02
	 * @author HJM
	 * @param amount	-	int 날짜 ( 예: -1 = 어제 / 0 = 오늘 / 1 = 내일 )
	 * @return
	 * @throws BaseException
	 */
	public String getDate(int amount) throws BaseException 
	{
		String strDate = "";
		
		SimpleDateFormat sdf 	= 	new SimpleDateFormat("yyyy-MM-dd");
		Calendar cd 			= 	Calendar.getInstance();
		
		try
		{
			if(amount != 0)
				cd.add(Calendar.DAY_OF_YEAR, amount);
			
			strDate 			= 	sdf.format(cd.getTime());
			
			return strDate;
		}
		catch(Exception e)
		{
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		}
	}
	

	/**
	 * <pre>
	 * 현재 月의 1일
	 * </pre>
	 * @since	2009/02/02
	 * @author Yang D
	 * @return	해당 月의 1일 	- String
	 */
	public String getMonthOfFirstDay() {
		
		SimpleDateFormat 	sdf = new SimpleDateFormat("yyyy-MM-dd");
		
		Calendar 			cd 	= Calendar.getInstance();
		
		cd.set(Calendar.DATE, cd.getActualMinimum(Calendar.DATE));
		
		return sdf.format(cd.getTime());
	}
	
	
	/**
	 * <pre>
	 * 대표코드 update
	 * </pre>
	 * @throws BaseException
	 */
	public void updateHighCode(String cd_nm , String etc , String use_yn , String group_cd) throws BaseException {
		
		try {
			
			con	=	super.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("	UPDATE                      	\r\n");
			sql.append("			TB_COMM_CD          	\r\n");
			sql.append("	SET		                    	\r\n");
			sql.append("			CD_NM		=	?   	\r\n");
			sql.append("		,	ETC			=	?   	\r\n");
			sql.append("		,	USE_YN		=	?   	\r\n");
			sql.append("	WHERE                       	\r\n");
			sql.append("			GROUP_CD	=	?   	\r\n");
			sql.append("		AND                     	\r\n");
			sql.append("			CD = '0'            	\r\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("그룹코드('" + group_cd + "')를 변경하는 쿼리.\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			int idx = 1;
			pstmt.setString(idx++, cd_nm);		//대표코드명
			pstmt.setString(idx++, etc);		//비고
			pstmt.setString(idx++, use_yn);		//사용여부
			pstmt.setString(idx++, group_cd);	//대표코드
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			int i	= 	pstmt.executeUpdate();
			
			if (i > 0) {
				setMsg("OK");
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
	}

	/**
	 * <pre>
	 * 부서권한이 있는 팀을 조회함.
	 * 
	 * </pre>
	 * @throws BaseException
	 */
	public void retrieveTeamByAuthDept(String group_cd) throws BaseException {
		
		try {
			con	=	super.getDataSource().getConnection();
			
			sql.setLength(0);
			
			sql.append("	SELECT                                  			\r\n");
			sql.append("			GROUP_CD , CD , CD_NM , ETC , USE_YN		\r\n");
			sql.append("	FROM                                    			\r\n");
			sql.append("			TB_COMM_CD                      			\r\n");
			sql.append("	WHERE	                                			\r\n");
			sql.append("			GROUP_CD	= ?                 			\r\n");
			sql.append("		AND	                                			\r\n");
			sql.append("			CD			= '0'               			\r\n");
			
			System.out.println("------------------------------------------------------\r\n");
			System.out.println("그룹코드('" + group_cd + "')를 조회한다.\r\n" + sql.toString());
			System.out.println("------------------------------------------------------\r\n");
			
			pstmt 	= 	con.prepareStatement(sql.toString());
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		S T A R T
			/**************************************************************************/
			
			pstmt.setString(1, group_cd);		//그룹코드
			
			/**************************************************************************/
			//P A R A M E T E R		V A L U E S		E   N   D
			/**************************************************************************/
			
			rs	= 	pstmt.executeQuery();
			
			hm_high_cd = new HashMap<String, String>();
			
			if (rs.next()) {
				
				hm_high_cd.put("group_cd"	, rs.getString("GROUP_CD"));
				hm_high_cd.put("cd"			, rs.getString("CD"));
				hm_high_cd.put("cd_nm"		, rs.getString("CD_NM"));
				hm_high_cd.put("etc"		, rs.getString("ETC"));
				hm_high_cd.put("use_yn"		, rs.getString("USE_YN"));
			}
			
		} catch (SQLException se) {
			se.printStackTrace();
			BaseException dex = new BaseException();                
			dex.setException(se);
			dex.setAppReturnCode("10");
			throw dex;
		} catch(Exception e) {
			BaseException dex = new BaseException();                
			dex.setException(e);
			dex.setAppReturnCode("20");
			throw dex;
		} finally {
			try {
				if (pstmt != null) pstmt.close();
				if (rs != null) rs.close();
				if(con != null) con.close();
				
			} catch(SQLException fse) {
				BaseException dex = new BaseException();                
				dex.setException(fse);
				dex.setAppReturnCode("10");
				throw dex;
			}          
		}
		
	}


	
	/****************************GETTER / SETTER******************************/
	
	public List<HashMap<String, String>> getList() {
		return list;
	}

	public void setList(List<HashMap<String, String>> list) {
		this.list = list;
	}

	public String getYyyymmdd() {
		return yyyymmdd;
	}

	public void setYyyymmdd(String yyyymmdd) {
		this.yyyymmdd = yyyymmdd;
	}

	public String getReqYyyymmdd() {
		return reqYyyymmdd;
	}

	public void setReqYyyymmdd(String reqYyyymmdd) {
		this.reqYyyymmdd = reqYyyymmdd;
	}

	public String getYyyy_mm_dd() {
		return yyyy_mm_dd;
	}

	public void setYyyy_mm_dd(String yyyy_mm_dd) {
		this.yyyy_mm_dd = yyyy_mm_dd;
	}

	public int getRowCount() {
		return rowCount;
	}

	public void setRowCount(int rowCount) {
		this.rowCount = rowCount;
	}

	public String getMsg() {
		return msg;
	}

	public void setMsg(String msg) {
		this.msg = msg;
	}

	public String getMaxGroupCd() {
		return maxGroupCd;
	}

	public void setMaxGroupCd(String maxGroupCd) {
		this.maxGroupCd = maxGroupCd;
	}

	public String getMaxGroupCdPlusOne() {
		return maxGroupCdPlusOne;
	}

	public void setMaxGroupCdPlusOne(String maxGroupCdPlusOne) {
		this.maxGroupCdPlusOne = maxGroupCdPlusOne;
	}

	public HashMap<String, String> getHm_high_cd() {
		return hm_high_cd;
	}

	public void setHm_high_cd(HashMap<String, String> hm_high_cd) {
		this.hm_high_cd = hm_high_cd;
	}

	public String getCrud_type() {
		return crud_type;
	}

	public void setCrud_type(String crud_type) {
		this.crud_type = crud_type;
	}

	public List<HashMap<String, String>> getAlloclist() {
		return alloclist;
	}

	public void setAlloclist(List<HashMap<String, String>> alloclist) {
		this.alloclist = alloclist;
	}

	public String getVaca_in_out_tp() {
		return vaca_in_out_tp;
	}

	public void setVaca_in_out_tp(String vacaInOutTp) {
		vaca_in_out_tp = vacaInOutTp;
	}

	/****************************GETTER / SETTER******************************/
}
